﻿//using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;

//using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.POIFS.FileSystem;
using NUnit.Framework;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Threading;

namespace AEot
{
    public partial class Form1 : Form
    {
        public uploadForm uploadForm;
        public helpForm helpForm;

        #region 声明字典列表变量、INI文件路径

        public Dictionary<int, string> namelist = new Dictionary<int, string>();
        public Dictionary<int, string> locationlist = new Dictionary<int, string>();
        public Dictionary<int, string> phonenumberlist = new Dictionary<int, string>();
        public Dictionary<int, bool> ot7timelist = new Dictionary<int, bool>();
        public Dictionary<int, bool> ot8timelist = new Dictionary<int, bool>();
        public Dictionary<int, bool> ot9timelist = new Dictionary<int, bool>();
        public Dictionary<int, bool> weekendot8timelist = new Dictionary<int, bool>();
        public Dictionary<int, bool> weekendot12timelist = new Dictionary<int, bool>();
        public Dictionary<int, bool> weekendot17timelist = new Dictionary<int, bool>();

        public string iniFile = "C:\\Users\\ae.ini";

        #endregion 声明字典列表变量、INI文件路径

        public Form1()
        {
            uploadForm = new uploadForm();
            uploadForm.Show();
            //uploadForm.ShowSplashScreen();
            InitializeComponent();
            timer1.Start();
        }

        /// <summary>
        /// 打开程序时，从INI文件读取信息（住址、号码）
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e">窗体上载时候执行</param>
        private void Form1_Load(object sender, EventArgs e)
        {
            INIinitial();
        }

        /// <summary>
        /// 从INI文件读取信息（住址、号码）,初始化地址、号码文本
        /// </summary>
        public void INIinitial()
        {
            LocatioinBox1.Text = Win32API.INIGetStringValue(iniFile, "Gabriel", "Location", "location");
            PhoneBox1.Text = Win32API.INIGetStringValue(iniFile, "Gabriel", "PhoneNumber", "PhoneNumber");
            LocatioinBox2.Text = Win32API.INIGetStringValue(iniFile, "Artorias", "Location", "location");
            PhoneBox2.Text = Win32API.INIGetStringValue(iniFile, "Artorias", "PhoneNumber", "PhoneNumber");
            LocatioinBox3.Text = Win32API.INIGetStringValue(iniFile, "Alan Zhang", "Location", "location");
            PhoneBox3.Text = Win32API.INIGetStringValue(iniFile, "Alan Zhang", "PhoneNumber", "PhoneNumber");
            LocatioinBox4.Text = Win32API.INIGetStringValue(iniFile, "Jasper", "Location", "location");
            PhoneBox4.Text = Win32API.INIGetStringValue(iniFile, "Jasper", "PhoneNumber", "PhoneNumber");
            LocatioinBox5.Text = Win32API.INIGetStringValue(iniFile, "Mike", "Location", "location");
            PhoneBox5.Text = Win32API.INIGetStringValue(iniFile, "Mike", "PhoneNumber", "PhoneNumber");
            LocatioinBox6.Text = Win32API.INIGetStringValue(iniFile, "Roc", "Location", "location");
            PhoneBox6.Text = Win32API.INIGetStringValue(iniFile, "Roc", "PhoneNumber", "PhoneNumber");
            LocatioinBox7.Text = Win32API.INIGetStringValue(iniFile, "Sky Song", "Location", "location");
            PhoneBox7.Text = Win32API.INIGetStringValue(iniFile, "Sky Song", "PhoneNumber", "PhoneNumber");
            LocatioinBox8.Text = Win32API.INIGetStringValue(iniFile, "Thomas", "Location", "location");
            PhoneBox8.Text = Win32API.INIGetStringValue(iniFile, "Thomas", "PhoneNumber", "PhoneNumber");
            LocatioinBox9.Text = Win32API.INIGetStringValue(iniFile, "Kyle", "Location", "location");
            PhoneBox9.Text = Win32API.INIGetStringValue(iniFile, "Kyle", "PhoneNumber", "PhoneNumber");
            LocatioinBox10.Text = Win32API.INIGetStringValue(iniFile, "William", "Location", "location");
            PhoneBox10.Text = Win32API.INIGetStringValue(iniFile, "William", "PhoneNumber", "PhoneNumber");
            username.Text = Win32API.INIGetStringValue(iniFile, "user", "useraddress", "useraddress");
        }

        /// <summary>
        /// 当关闭窗体时，自动保存INI信息
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e">窗体关闭中执行</param>
        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            ListUpload();
            INIsave();
            ListClear();
        }

        /// <summary>
        /// 将姓名信息、地址信息、号码信息依次填到字典列表中
        /// </summary>
        public void ListUpload()
        {
            namelist.Add(0, namelabel1.Text);
            namelist.Add(1, namelabel2.Text);
            namelist.Add(2, namelabel3.Text);
            namelist.Add(3, namelabel4.Text);
            namelist.Add(4, namelabel5.Text);
            namelist.Add(5, namelabel6.Text);
            namelist.Add(6, namelabel7.Text);
            namelist.Add(7, namelabel8.Text);
            namelist.Add(8, namelabel9.Text);
            namelist.Add(9, namelabel10.Text);

            locationlist.Add(0, LocatioinBox1.Text);
            locationlist.Add(1, LocatioinBox2.Text);
            locationlist.Add(2, LocatioinBox3.Text);
            locationlist.Add(3, LocatioinBox4.Text);
            locationlist.Add(4, LocatioinBox5.Text);
            locationlist.Add(5, LocatioinBox6.Text);
            locationlist.Add(6, LocatioinBox7.Text);
            locationlist.Add(7, LocatioinBox8.Text);
            locationlist.Add(8, LocatioinBox9.Text);
            locationlist.Add(9, LocatioinBox10.Text);

            phonenumberlist.Add(0, PhoneBox1.Text);
            phonenumberlist.Add(1, PhoneBox2.Text);
            phonenumberlist.Add(2, PhoneBox3.Text);
            phonenumberlist.Add(3, PhoneBox4.Text);
            phonenumberlist.Add(4, PhoneBox5.Text);
            phonenumberlist.Add(5, PhoneBox6.Text);
            phonenumberlist.Add(6, PhoneBox7.Text);
            phonenumberlist.Add(7, PhoneBox8.Text);
            phonenumberlist.Add(8, PhoneBox9.Text);
            phonenumberlist.Add(9, PhoneBox10.Text);
        }

        /// <summary>
        /// 清除所有列表存储
        /// </summary>
        public void ListClear()
        {
            namelist.Clear();
            locationlist.Clear();
            phonenumberlist.Clear();
            ot7timelist.Clear();
            ot8timelist.Clear();
            ot9timelist.Clear();
            weekendot12timelist.Clear();
            weekendot8timelist.Clear();
            weekendot17timelist.Clear();
        }

        /// <summary>
        /// 保存INI信息
        /// </summary>
        public void INIsave()
        {
            for (int i = 0; i < 10; i++)
            {
                Win32API.INIWriteValue(iniFile, namelist[i].ToString(), "Location", locationlist[i]);
                Win32API.INIWriteValue(iniFile, namelist[i].ToString(), "PhoneNumber", phonenumberlist[i]);
            }
            Win32API.INIWriteValue(iniFile, "user", "useraddress", username.Text);
        }

        /// <summary>
        /// 生成Excel表格
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e">点生成表格按钮</param>
        private void Button1_Click(object sender, EventArgs e)
        {
            ListUpload();
            OTtimelistUPload();
            int datestring = int.Parse(DateTime.Now.Day.ToString());
            int month = int.Parse(DateTime.Now.Month.ToString());
            string monthstring;
            if (datestring > 24)
            {
                month = month + 1;
            }
            switch (month)
            {
                case 1:
                    monthstring = "Jan";
                    break;

                case 2:
                    monthstring = "Feb";
                    break;

                case 3:
                    monthstring = "Mar";
                    break;

                case 4:
                    monthstring = "Apr";
                    break;

                case 5:
                    monthstring = "May";
                    break;

                case 6:
                    monthstring = "June";
                    break;

                case 7:
                    monthstring = "July";
                    break;

                case 8:
                    monthstring = "Aug";
                    break;

                case 9:
                    monthstring = "Sep";
                    break;

                case 10:
                    monthstring = "Oct";
                    break;

                case 11:
                    monthstring = "Nov";
                    break;

                case 12:
                    monthstring = "Dec";
                    break;

                default:
                    monthstring = "Jan";
                    break;
            }
            CreatWorkweekExcel("C:\\Users\\" + username.Text + "\\Desktop\\OT application form-AE-" + monthstring + ".xlsx");
            ListClear();
        }

        /// <summary>
        /// 将加班信息依次填到字典列表中
        /// </summary>
        public void OTtimelistUPload()
        {
            ot7timelist.Add(0, checkBox1.Checked);
            ot7timelist.Add(1, checkBox2.Checked);
            ot7timelist.Add(2, checkBox3.Checked);
            ot7timelist.Add(3, checkBox4.Checked);
            ot7timelist.Add(4, checkBox5.Checked);
            ot7timelist.Add(5, checkBox6.Checked);
            ot7timelist.Add(6, checkBox7.Checked);
            ot7timelist.Add(7, checkBox8.Checked);
            ot7timelist.Add(8, checkBox9.Checked);
            ot7timelist.Add(9, checkBox10.Checked);

            ot8timelist.Add(0, checkBox11.Checked);
            ot8timelist.Add(1, checkBox12.Checked);
            ot8timelist.Add(2, checkBox13.Checked);
            ot8timelist.Add(3, checkBox14.Checked);
            ot8timelist.Add(4, checkBox15.Checked);
            ot8timelist.Add(5, checkBox16.Checked);
            ot8timelist.Add(6, checkBox17.Checked);
            ot8timelist.Add(7, checkBox18.Checked);
            ot8timelist.Add(8, checkBox19.Checked);
            ot8timelist.Add(9, checkBox20.Checked);

            ot9timelist.Add(0, checkBox21.Checked);
            ot9timelist.Add(1, checkBox22.Checked);
            ot9timelist.Add(2, checkBox23.Checked);
            ot9timelist.Add(3, checkBox24.Checked);
            ot9timelist.Add(4, checkBox25.Checked);
            ot9timelist.Add(5, checkBox26.Checked);
            ot9timelist.Add(6, checkBox27.Checked);
            ot9timelist.Add(7, checkBox28.Checked);
            ot9timelist.Add(8, checkBox29.Checked);
            ot9timelist.Add(9, checkBox30.Checked);
        }

        /// <summary>
        /// 填写、保存Excel
        /// </summary>
        /// <param name="filepath">Excel保存名称、路径</param>
        public void CreatWorkweekExcel(string filepath)
        {
            string excelfilepath = filepath;
            //FileStream readfile;
            //if (File.Exists(excelfilepath))
            //{
            //    readfile = new FileStream(excelfilepath, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite);//读取流
            //}
            //else
            //{
            //    readfile = new FileStream(excelfilepath, FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite);//读取流
            //}
            //readfile.Position = 0;
            IWorkbook wb;
            wb = new XSSFWorkbook();
            //readfile.Close();

            //if (true)

            #region 设定Excel样式

            //创建工作薄
            ICellStyle style1 = wb.CreateCellStyle();//样式
            style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//文字水平对齐方式
            style1.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//文字垂直对齐方式
            //设置边框
            style1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            style1.WrapText = true;//自动换行

            IFont font1 = wb.CreateFont();//字体
            font1.FontName = "楷体";
            //font1.Color = XSSFColor.Black;
            //font1.Color = HSSFColor.Red.Index;//字体颜色
            /*font1.Boldweight = (short)FontBoldWeight.Normal;*///字体加粗样式

            ICellStyle dateStyle = wb.CreateCellStyle();//样式
            dateStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;//文字水平对齐方式
            dateStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//文字垂直对齐方式
                                                                                     //设置数据显示格式
            IDataFormat dataFormatCustom = wb.CreateDataFormat();
            dateStyle.DataFormat = dataFormatCustom.GetFormat("yyyy-MM-dd HH:mm:ss");

            #endregion 设定Excel样式

            //创建一个表单
            string datestring = DateTime.Now.ToString("yyyy-MM-dd");
            ISheet sheet;
            if (int.Parse(wb.GetSheetIndex(datestring).ToString()) < 0)
            {
                int q = int.Parse(wb.GetSheetIndex(datestring).ToString());
                sheet = wb.CreateSheet(datestring);
                //sheet = wb.GetSheet(datestring);
            }
            else
            {
                int q = int.Parse(wb.GetSheetIndex(datestring).ToString());
                //sheet = wb.CreateSheet(datestring);
                sheet = wb.GetSheet(datestring);
            }
            //ISheet sheet = wb.CreateSheet(datestring);

            //设置列宽
            int[] columnWidth = { 25, 30, 30, 30, 30, 30, 40, 40, 40 };
            for (int i = 0; i < columnWidth.Length; i++)
            {
                sheet.SetColumnWidth(i, 100 * columnWidth[i]);
            }

            //测试数据
            int rowCount = 12, columnCount = 9;
            object[,] data = {
                {"Workweek OT Information", " ", " ", " ", " ", " ", " ", " ", " "},
                {"Dept", "Name", "OT Off Time 7:00pm ", " OT Off Time 8:00pm", " OT Off Time 9:00pm ", " Need Taxi(Y/N)", "Need meal at Canteen(Y/N) ", "Drop Off Location ", "Cell Phone "},
                {"AE", " ", " ", " ", " ", "V ", "V", " ", " "},//gabriel
                {"AE", " ", " ", " ", " ", "V ", "V ", " ", " "},//art
                {"AE", " ", " ", " ", " ", "V ", "V ", " ", " "},//alan
                {"AE", " ", " ", " ", " ", "V ", "V ", " ", " "},//jasper
                {"AE", " ", " ", " ", " ", "V ", "V ", " ", " "},//mike
                {"AE", " ", " ", " ", " ", "V ", "V ", " ", " "},//roc
                {"AE", " ", " ", " ", " ", "V ", "V ", " ", " "},//sky
                {"AE", " ", " ", " ", " ", "V ", "V ", " ", " "},//thomas
                {"AE", " ", " ", " ", " ", "V ", "V ", " ", " "},//kyle
                {"AE", " ", " ", " ", " ", "V ", "V ", " ", " "}//
                };
            //日期可以直接传字符串，NPOI会自动识别
            //如果是DateTime类型，则要设置CellStyle.DataFormat，否则会显示为数字

            IRow row;
            ICell cell;

            for (int i = 0; i < rowCount; i++)
            {
                row = sheet.CreateRow(i);//创建第i行
                for (int j = 0; j < columnCount; j++)
                {
                    cell = row.CreateCell(j);//创建第j列
                    cell.CellStyle = style1;
                    //根据数据类型设置不同类型的cell
                    object obj = data[i, j];
                    SetCellValue(cell, data[i, j]);
                    //如果是日期，则设置日期显示的格式
                    if (obj.GetType() == typeof(DateTime))
                    {
                        cell.CellStyle = dateStyle;
                    }
                    //如果要根据内容自动调整列宽，需要先setCellValue再调用

                    #region 填表部分

                    //填名字
                    if (i > 1 && j == 1)
                    {
                        SetCellValue(cell, namelist[i - 2]);
                    }
                    //7点加班
                    if (i > 1 && j == 2)
                    {
                        if (ot7timelist[i - 2] == true)
                        {
                            SetCellValue(cell, "V");
                        }
                    }
                    //8点加班
                    if (i > 1 && j == 3)
                    {
                        if (ot8timelist[i - 2] == true)
                        {
                            SetCellValue(cell, "V");
                        }
                    }
                    //9点加班
                    if (i > 1 && j == 4)
                    {
                        if (ot9timelist[i - 2] == true)
                        {
                            SetCellValue(cell, "V");
                        }
                    }
                    //填地点
                    if (i > 1 && j == 7)
                    {
                        SetCellValue(cell, locationlist[i - 2]);
                    }
                    //填号码
                    if (i > 1 && j == 8)
                    {
                        SetCellValue(cell, phonenumberlist[i - 2]);
                    }

                    #endregion 填表部分
                }

                #region 隐藏没有加班的部分

                if (i > 1)
                {
                    //如果7,8,9点三列都为空，即可判定没有加班，可以隐藏
                    if (row.GetCell(2).ToString() != "V" && row.GetCell(3).ToString() != "V" && row.GetCell(4).ToString() != "V")
                    {
                        row.ZeroHeight = true;//行高度为0=隐藏行
                    }
                }

                #endregion 隐藏没有加班的部分
            }

            //合并单元格，如果要合并的单元格中都有数据，只会保留左上角的
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, 8);
            sheet.AddMergedRegion(region);

            //using (FileStream fileout = new FileStream(excelfilepath, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite)) //打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件！
            using (FileStream fileout = File.OpenWrite(excelfilepath)) //打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件！
            {
                //fileout.Position = 0;
                //fileout.Flush();
                wb.Write(fileout);   //向打开的这个xls文件中写入mySheet表并保存。
                wb.Close();
                fileout.Close();
            }
            GC.Collect();
        }

        /// <summary>
        /// 根据数据类型设置不同类型的cell
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="obj"></param>
        public static void SetCellValue(ICell cell, object obj)
        {
            if (obj.GetType() == typeof(int))
            {
                cell.SetCellValue((int)obj);
            }
            else if (obj.GetType() == typeof(double))
            {
                cell.SetCellValue((double)obj);
            }
            else if (obj.GetType() == typeof(IRichTextString))
            {
                cell.SetCellValue((IRichTextString)obj);
            }
            else if (obj.GetType() == typeof(string))
            {
                cell.SetCellValue(obj.ToString());
            }
            else if (obj.GetType() == typeof(DateTime))
            {
                cell.SetCellValue((DateTime)obj);
            }
            else if (obj.GetType() == typeof(bool))
            {
                cell.SetCellValue((bool)obj);
            }
            else
            {
                cell.SetCellValue(obj.ToString());
            }
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            uploadForm.Dispose();
            this.WindowState = FormWindowState.Normal;
            timer1.Stop();//添加计时器停止，修复了窗口跳出的问题
        }

        private void toolStripButton1_Click(object sender, EventArgs e)
        {
            helpForm = new helpForm();
            helpForm.Show();
        }

        /// <summary>
        /// 周末加班时间填表
        /// </summary>
        public void WeekendOTtime()
        {
            weekendot8timelist.Add(0, checkBox31.Checked);
            weekendot8timelist.Add(1, checkBox32.Checked);
            weekendot8timelist.Add(2, checkBox33.Checked);
            weekendot8timelist.Add(3, checkBox34.Checked);
            weekendot8timelist.Add(4, checkBox35.Checked);
            weekendot8timelist.Add(5, checkBox36.Checked);
            weekendot8timelist.Add(6, checkBox37.Checked);
            weekendot8timelist.Add(7, checkBox38.Checked);
            weekendot8timelist.Add(8, checkBox39.Checked);
            weekendot8timelist.Add(9, checkBox40.Checked);

            weekendot12timelist.Add(0, checkBox41.Checked);
            weekendot12timelist.Add(1, checkBox42.Checked);
            weekendot12timelist.Add(2, checkBox43.Checked);
            weekendot12timelist.Add(3, checkBox44.Checked);
            weekendot12timelist.Add(4, checkBox45.Checked);
            weekendot12timelist.Add(5, checkBox46.Checked);
            weekendot12timelist.Add(6, checkBox47.Checked);
            weekendot12timelist.Add(7, checkBox48.Checked);
            weekendot12timelist.Add(8, checkBox49.Checked);
            weekendot12timelist.Add(9, checkBox50.Checked);

            weekendot17timelist.Add(0, checkBox51.Checked);
            weekendot17timelist.Add(1, checkBox52.Checked);
            weekendot17timelist.Add(2, checkBox53.Checked);
            weekendot17timelist.Add(3, checkBox54.Checked);
            weekendot17timelist.Add(4, checkBox55.Checked);
            weekendot17timelist.Add(5, checkBox56.Checked);
            weekendot17timelist.Add(6, checkBox57.Checked);
            weekendot17timelist.Add(7, checkBox58.Checked);
            weekendot17timelist.Add(8, checkBox59.Checked);
            weekendot17timelist.Add(9, checkBox60.Checked);
        }

        private void Button2_Click(object sender, EventArgs e)
        {
            ListUpload();
            WeekendOTtime();
            CreatWeekendExcel("C:\\Users\\" + username.Text + "\\Desktop\\Weekend or Holiday AE OT Information" + ".xlsx");
            ListClear();
        }

        public void CreatWeekendExcel(string filepath)
        {
            string excelfilepath = filepath;
            //创建工作薄
            IWorkbook wb;
            wb = new XSSFWorkbook();

            #region 设定Excel样式

            ICellStyle style1 = wb.CreateCellStyle();//样式
            style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//文字水平对齐方式
            style1.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//文字垂直对齐方式
            //设置边框
            style1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            style1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            style1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            style1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            style1.WrapText = true;//自动换行

            IFont font1 = wb.CreateFont();//字体
            font1.FontName = "楷体";
            //font1.Color = HSSFColor.Red.Index;//字体颜色
            /*font1.Boldweight = (short)FontBoldWeight.Normal;*///字体加粗样式

            ICellStyle dateStyle = wb.CreateCellStyle();//样式
            dateStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;//文字水平对齐方式
            dateStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//文字垂直对齐方式
                                                                                     //设置数据显示格式
            IDataFormat dataFormatCustom = wb.CreateDataFormat();
            dateStyle.DataFormat = dataFormatCustom.GetFormat("yyyy-MM-dd HH:mm:ss");

            #endregion 设定Excel样式

            //创建一个表单
            int datestring = int.Parse(DateTime.Now.ToString("dd"));
            string weekstring = DateTime.Now.DayOfWeek.ToString();
            if (weekstring == "Friday")
            {
                datestring = datestring + 1;
            }
            ISheet sheet = wb.CreateSheet(DateTime.Now.ToString("yyyy-MM") + "-" + datestring.ToString());
            //设置列宽
            int[] columnWidth = { 25, 30, 30, 30, 30, 30, 40, 40, 40 };
            for (int i = 0; i < columnWidth.Length; i++)
            {
                sheet.SetColumnWidth(i, 100 * columnWidth[i]);
            }

            //测试数据
            int rowCount = 12, columnCount = 9;
            object[,] data = {
                {"Weekend or Holiday AE OT Information", " ", " ", " ", " ", " ", " ", " ", " "},
                {"Dept", "Name", "OT On Time 8:00am ", " OT Off Time 12:00pm", " OT Off Time 17:00pm ", " Need Taxi(Y/N)", "Need meal at Canteen(Y/N) ", "Drop Off Location ", "Cell Phone "},
                {"AE", " ", " ", " ", " ", "V ", "V", " ", " "},//gabriel
                {"AE", " ", " ", " ", " ", "V ", "V ", " ", " "},//art
                {"AE", " ", " ", " ", " ", "V ", "V ", " ", " "},//alan
                {"AE", " ", " ", " ", " ", "V ", "V ", " ", " "},//jasper
                {"AE", " ", " ", " ", " ", "V ", "V ", " ", " "},//mike
                {"AE", " ", " ", " ", " ", "V ", "V ", " ", " "},//roc
                {"AE", " ", " ", " ", " ", "V ", "V ", " ", " "},//sky
                {"AE", " ", " ", " ", " ", "V ", "V ", " ", " "},//thomas
                {"AE", " ", " ", " ", " ", "V ", "V ", " ", " "},//kyle
                {"AE", " ", " ", " ", " ", "V ", "V ", " ", " "}//loyper
                };
            //日期可以直接传字符串，NPOI会自动识别
            //如果是DateTime类型，则要设置CellStyle.DataFormat，否则会显示为数字

            IRow row;
            ICell cell;

            for (int i = 0; i < rowCount; i++)
            {
                row = sheet.CreateRow(i);//创建第i行
                for (int j = 0; j < columnCount; j++)
                {
                    cell = row.CreateCell(j);//创建第j列
                    cell.CellStyle = style1;
                    //根据数据类型设置不同类型的cell
                    object obj = data[i, j];
                    SetCellValue(cell, data[i, j]);
                    //如果是日期，则设置日期显示的格式
                    if (obj.GetType() == typeof(DateTime))
                    {
                        cell.CellStyle = dateStyle;
                    }
                    //如果要根据内容自动调整列宽，需要先setCellValue再调用

                    #region 填表部分

                    //填名字
                    if (i > 1 && j == 1)
                    {
                        SetCellValue(cell, namelist[i - 2]);
                    }
                    //周末8点加班上班
                    if (i > 1 && j == 2)
                    {
                        if (weekendot8timelist[i - 2] == true)
                        {
                            SetCellValue(cell, "V");
                        }
                    }
                    //12点下班
                    if (i > 1 && j == 3)
                    {
                        if (weekendot12timelist[i - 2] == true)
                        {
                            SetCellValue(cell, "V");
                        }
                    }
                    //17点下班
                    if (i > 1 && j == 4)
                    {
                        if (weekendot17timelist[i - 2] == true)
                        {
                            SetCellValue(cell, "V");
                        }
                    }
                    //填地点
                    if (i > 1 && j == 7)
                    {
                        SetCellValue(cell, locationlist[i - 2]);
                    }
                    //填号码
                    if (i > 1 && j == 8)
                    {
                        SetCellValue(cell, phonenumberlist[i - 2]);
                    }

                    #endregion 填表部分
                }

                #region 隐藏没有加班的部分

                if (i > 1)
                {
                    //如果7,8,9点三列都为空，即可判定没有加班，可以隐藏
                    if (row.GetCell(2).ToString() != "V" && row.GetCell(3).ToString() != "V" && row.GetCell(4).ToString() != "V")
                    {
                        row.ZeroHeight = true;//行高度为0=隐藏行
                    }
                }

                #endregion 隐藏没有加班的部分
            }

            //合并单元格，如果要合并的单元格中都有数据，只会保留左上角的

            //CellRangeAddress(0, 2, 0, 0)，合并0-2行，0-0列的单元格
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, 8);
            sheet.AddMergedRegion(region);

            using (FileStream fileout = File.OpenWrite(excelfilepath)) //打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件！
            {
                //fileout.Position = 0;
                //fileout.Flush();
                wb.Write(fileout);   //向打开的这个xls文件中写入mySheet表并保存。
                wb.Close();
                fileout.Close();
            }
            GC.Collect();
            //try
            //{
            //    FileStream fs = File.OpenWrite(excelfilepath);
            //    wb.Write(fs);//向打开的这个Excel文件中写入表单并保存。
            //    fs.Close();
            //    wb.Close();
            //}
            //catch (Exception e)
            //{
            //    throw;            //Debug.WriteLine(e.Message);
            //}
        }
    }

    public class Win32API
    {
        #region INI文件操作

        /*
         * 针对INI文件的API操作方法，其中的节点（Section)、键（KEY）都不区分大小写
         * 如果指定的INI文件不存在，会自动创建该文件。
         *
         * CharSet定义的时候使用了什么类型，在使用相关方法时必须要使用相应的类型
         *      例如 GetPrivateProfileSectionNames声明为CharSet.Auto,那么就应该使用 Marshal.PtrToStringAuto来读取相关内容
         *      如果使用的是CharSet.Ansi，就应该使用Marshal.PtrToStringAnsi来读取内容
         *
         */

        #region API声明

        /// <summary>
        /// 获取所有节点名称(Section)
        /// </summary>
        /// <param name="lpszReturnBuffer">存放节点名称的内存地址,每个节点之间用\0分隔</param>
        /// <param name="nSize">内存大小(characters)</param>
        /// <param name="lpFileName">Ini文件</param>
        /// <returns>内容的实际长度,为0表示没有内容,为nSize-2表示内存大小不够</returns>
        [DllImport("kernel32.dll", CharSet = CharSet.Auto)]
        private static extern uint GetPrivateProfileSectionNames(IntPtr lpszReturnBuffer, uint nSize, string lpFileName);

        /// <summary>
        /// 获取某个指定节点(Section)中所有KEY和Value
        /// </summary>
        /// <param name="lpAppName">节点名称</param>
        /// <param name="lpReturnedString">返回值的内存地址,每个之间用\0分隔</param>
        /// <param name="nSize">内存大小(characters)</param>
        /// <param name="lpFileName">Ini文件</param>
        /// <returns>内容的实际长度,为0表示没有内容,为nSize-2表示内存大小不够</returns>
        [DllImport("kernel32.dll", CharSet = CharSet.Auto)]
        private static extern uint GetPrivateProfileSection(string lpAppName, IntPtr lpReturnedString, uint nSize, string lpFileName);

        /// <summary>
        /// 读取INI文件中指定的Key的值
        /// </summary>
        /// <param name="lpAppName">节点名称。如果为null,则读取INI中所有节点名称,每个节点名称之间用\0分隔</param>
        /// <param name="lpKeyName">Key名称。如果为null,则读取INI中指定节点中的所有KEY,每个KEY之间用\0分隔</param>
        /// <param name="lpDefault">读取失败时的默认值</param>
        /// <param name="lpReturnedString">读取的内容缓冲区，读取之后，多余的地方使用\0填充</param>
        /// <param name="nSize">内容缓冲区的长度</param>
        /// <param name="lpFileName">INI文件名</param>
        /// <returns>实际读取到的长度</returns>
        [DllImport("kernel32.dll", CharSet = CharSet.Auto)]
        private static extern uint GetPrivateProfileString(string lpAppName, string lpKeyName, string lpDefault, [In, Out] char[] lpReturnedString, uint nSize, string lpFileName);

        //另一种声明方式,使用 StringBuilder 作为缓冲区类型的缺点是不能接受\0字符，会将\0及其后的字符截断,
        //所以对于lpAppName或lpKeyName为null的情况就不适用
        [DllImport("kernel32.dll", CharSet = CharSet.Auto)]
        private static extern uint GetPrivateProfileString(string lpAppName, string lpKeyName, string lpDefault, StringBuilder lpReturnedString, uint nSize, string lpFileName);

        //再一种声明，使用string作为缓冲区的类型同char[]
        [DllImport("kernel32.dll", CharSet = CharSet.Auto)]
        private static extern uint GetPrivateProfileString(string lpAppName, string lpKeyName, string lpDefault, string lpReturnedString, uint nSize, string lpFileName);

        /// <summary>
        /// 将指定的键值对写到指定的节点，如果已经存在则替换。
        /// </summary>
        /// <param name="lpAppName">节点，如果不存在此节点，则创建此节点</param>
        /// <param name="lpString">Item键值对，多个用\0分隔,形如key1=value1\0key2=value2
        /// <para>如果为string.Empty，则删除指定节点下的所有内容，保留节点</para>
        /// <para>如果为null，则删除指定节点下的所有内容，并且删除该节点</para>
        /// </param>
        /// <param name="lpFileName">INI文件</param>
        /// <returns>是否成功写入</returns>
        [DllImport("kernel32.dll", CharSet = CharSet.Auto)]
        [return: MarshalAs(UnmanagedType.Bool)]     //可以没有此行
        private static extern bool WritePrivateProfileSection(string lpAppName, string lpString, string lpFileName);

        /// <summary>
        /// 将指定的键和值写到指定的节点，如果已经存在则替换
        /// </summary>
        /// <param name="lpAppName">节点名称</param>
        /// <param name="lpKeyName">键名称。如果为null，则删除指定的节点及其所有的项目</param>
        /// <param name="lpString">值内容。如果为null，则删除指定节点中指定的键。</param>
        /// <param name="lpFileName">INI文件</param>
        /// <returns>操作是否成功</returns>
        [DllImport("kernel32.dll", CharSet = CharSet.Auto, SetLastError = true)]
        [return: MarshalAs(UnmanagedType.Bool)]
        private static extern bool WritePrivateProfileString(string lpAppName, string lpKeyName, string lpString, string lpFileName);

        #endregion API声明

        #region 封装

        /// <summary>
        /// 读取INI文件中指定INI文件中的所有节点名称(Section)
        /// </summary>
        /// <param name="iniFile">Ini文件</param>
        /// <returns>所有节点,没有内容返回string[0]</returns>
        public static string[] INIGetAllSectionNames(string iniFile)
        {
            uint MAX_BUFFER = 32767;    //默认为32767

            string[] sections = new string[0];      //返回值

            //申请内存
            IntPtr pReturnedString = Marshal.AllocCoTaskMem((int)MAX_BUFFER * sizeof(char));
            uint bytesReturned = Win32API.GetPrivateProfileSectionNames(pReturnedString, MAX_BUFFER, iniFile);
            if (bytesReturned != 0)
            {
                //读取指定内存的内容
                string local = Marshal.PtrToStringAuto(pReturnedString, (int)bytesReturned).ToString();

                //每个节点之间用\0分隔,末尾有一个\0
                sections = local.Split(new char[] { '\0' }, StringSplitOptions.RemoveEmptyEntries);
            }

            //释放内存
            Marshal.FreeCoTaskMem(pReturnedString);

            return sections;
        }

        /// <summary>
        /// 获取INI文件中指定节点(Section)中的所有条目(key=value形式)
        /// </summary>
        /// <param name="iniFile">Ini文件</param>
        /// <param name="section">节点名称</param>
        /// <returns>指定节点中的所有项目,没有内容返回string[0]</returns>
        public static string[] INIGetAllItems(string iniFile, string section)
        {
            //返回值形式为 key=value,例如 Color=Red
            uint MAX_BUFFER = 32767;    //默认为32767

            string[] items = new string[0];      //返回值

            //分配内存
            IntPtr pReturnedString = Marshal.AllocCoTaskMem((int)MAX_BUFFER * sizeof(char));

            uint bytesReturned = Win32API.GetPrivateProfileSection(section, pReturnedString, MAX_BUFFER, iniFile);

            if (!(bytesReturned == MAX_BUFFER - 2) || (bytesReturned == 0))
            {
                string returnedString = Marshal.PtrToStringAuto(pReturnedString, (int)bytesReturned);
                items = returnedString.Split(new char[] { '\0' }, StringSplitOptions.RemoveEmptyEntries);
            }

            Marshal.FreeCoTaskMem(pReturnedString);     //释放内存

            return items;
        }

        /// <summary>
        /// 获取INI文件中指定节点(Section)中的所有条目的Key列表
        /// </summary>
        /// <param name="iniFile">Ini文件</param>
        /// <param name="section">节点名称</param>
        /// <returns>如果没有内容,反回string[0]</returns>
        public static string[] INIGetAllItemKeys(string iniFile, string section)
        {
            string[] value = new string[0];
            const int SIZE = 1024 * 10;

            if (string.IsNullOrEmpty(section))
            {
                throw new ArgumentException("必须指定节点名称", "section");
            }

            char[] chars = new char[SIZE];
            uint bytesReturned = Win32API.GetPrivateProfileString(section, null, null, chars, SIZE, iniFile);

            if (bytesReturned != 0)
            {
                value = new string(chars).Split(new char[] { '\0' }, StringSplitOptions.RemoveEmptyEntries);
            }
            chars = null;

            return value;
        }

        /// <summary>
        /// 读取INI文件中指定KEY的字符串型值
        /// </summary>
        /// <param name="iniFile">Ini文件</param>
        /// <param name="section">节点名称</param>
        /// <param name="key">键名称</param>
        /// <param name="defaultValue">如果没此KEY所使用的默认值</param>
        /// <returns>读取到的值</returns>
        public static string INIGetStringValue(string iniFile, string section, string key, string defaultValue)
        {
            string value = defaultValue;
            const int SIZE = 1024 * 10;

            if (string.IsNullOrEmpty(section))
            {
                throw new ArgumentException("必须指定节点名称", "section");
            }

            if (string.IsNullOrEmpty(key))
            {
                throw new ArgumentException("必须指定键名称(key)", "key");
            }

            StringBuilder sb = new StringBuilder(SIZE);
            uint bytesReturned = Win32API.GetPrivateProfileString(section, key, defaultValue, sb, SIZE, iniFile);

            if (bytesReturned != 0)
            {
                value = sb.ToString();
            }
            sb = null;

            return value;
        }

        /// <summary>
        /// 在INI文件中，将指定的键值对写到指定的节点，如果已经存在则替换
        /// </summary>
        /// <param name="iniFile">INI文件</param>
        /// <param name="section">节点，如果不存在此节点，则创建此节点</param>
        /// <param name="items">键值对，多个用\0分隔,形如key1=value1\0key2=value2</param>
        /// <returns></returns>
        public static bool INIWriteItems(string iniFile, string section, string items)
        {
            if (string.IsNullOrEmpty(section))
            {
                throw new ArgumentException("必须指定节点名称", "section");
            }

            if (string.IsNullOrEmpty(items))
            {
                throw new ArgumentException("必须指定键值对", "items");
            }

            return Win32API.WritePrivateProfileSection(section, items, iniFile);
        }

        /// <summary>
        /// 在INI文件中，指定节点写入指定的键及值。如果已经存在，则替换。如果没有则创建。
        /// </summary>
        /// <param name="iniFile">INI文件</param>
        /// <param name="section">节点</param>
        /// <param name="key">键</param>
        /// <param name="value">值</param>
        /// <returns>操作是否成功</returns>
        public static bool INIWriteValue(string iniFile, string section, string key, string value)
        {
            if (string.IsNullOrEmpty(section))
            {
                throw new ArgumentException("必须指定节点名称", "section");
            }

            if (string.IsNullOrEmpty(key))
            {
                throw new ArgumentException("必须指定键名称", "key");
            }

            if (value == null)
            {
                throw new ArgumentException("值不能为null", "value");
            }

            return Win32API.WritePrivateProfileString(section, key, value, iniFile);
        }

        /// <summary>
        /// 在INI文件中，删除指定节点中的指定的键。
        /// </summary>
        /// <param name="iniFile">INI文件</param>
        /// <param name="section">节点</param>
        /// <param name="key">键</param>
        /// <returns>操作是否成功</returns>
        public static bool INIDeleteKey(string iniFile, string section, string key)
        {
            if (string.IsNullOrEmpty(section))
            {
                throw new ArgumentException("必须指定节点名称", "section");
            }

            if (string.IsNullOrEmpty(key))
            {
                throw new ArgumentException("必须指定键名称", "key");
            }

            return Win32API.WritePrivateProfileString(section, key, null, iniFile);
        }

        /// <summary>
        /// 在INI文件中，删除指定的节点。
        /// </summary>
        /// <param name="iniFile">INI文件</param>
        /// <param name="section">节点</param>
        /// <returns>操作是否成功</returns>
        public static bool INIDeleteSection(string iniFile, string section)
        {
            if (string.IsNullOrEmpty(section))
            {
                throw new ArgumentException("必须指定节点名称", "section");
            }

            return Win32API.WritePrivateProfileString(section, null, null, iniFile);
        }

        /// <summary>
        /// 在INI文件中，删除指定节点中的所有内容。
        /// </summary>
        /// <param name="iniFile">INI文件</param>
        /// <param name="section">节点</param>
        /// <returns>操作是否成功</returns>
        public static bool INIEmptySection(string iniFile, string section)
        {
            if (string.IsNullOrEmpty(section))
            {
                throw new ArgumentException("必须指定节点名称", "section");
            }

            return Win32API.WritePrivateProfileSection(section, string.Empty, iniFile);
        }

        #endregion 封装

        #endregion INI文件操作
    }
}